package com.example.util;


import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * @author Hongten
 * @created 2014-5-20
 */
public class ReadExcelUtil {
    
	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
    
    /**
     * read the Excel file
     * @param path the path of the Excel file
     * @return
     * @throws IOException
     */
    public static final List<String[]> readExcel(InputStream io, String fileType, int colLength) throws IOException {
        if (OFFICE_EXCEL_2003_POSTFIX.equals(fileType)) {
        	return readExcel(new HSSFWorkbook(io), colLength);
        } else  if(OFFICE_EXCEL_2010_POSTFIX.equals(fileType)) {
        	return readExcel(new XSSFWorkbook(io), colLength);
        }
		return null;
    }
 
    /**
     * 读excel
     * 
     * @author xiaozhijun
     * @Date: 2016年9月21日 下午3:33:06
     * @param workBook
     * @param colLength
     * @return
     */
    private static final List<String[]> readExcel(Workbook workBook,int colLength){
    	 List<String[]> list = new ArrayList<String[]>();
         // Read the Sheet
         for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
        	 Sheet xssfSheet = workBook.getSheetAt(numSheet);
             parseSheet(xssfSheet, colLength,list);
         }
         return list;
    }
    
    /**
     * 公用方法给xls和xlsm用
     * 
     * @author xiaozhijun
     * @Date: 2016年9月21日 下午3:16:03
     * @param sheet
     * @param colLength
     * @return
     */
    private static final void parseSheet(Sheet sheet,int colLength, List<String[]> rowList){
    	if (sheet == null) {
           return;
        }
        // Read the Row
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
            	String[] colValues = new String[colLength];
            	for(int i= 0; i < colLength; i++){
            		if(!JudgeEmpty.ObjectIsEmpty(row.getCell(i))){
            			colValues[i] =getCellValue(row.getCell(i));
            		}
            	}
            	
            	rowList.add(colValues);
            }
        }
    }
    
    private static String getCellValue(Cell cell) {  
        String ret;  
        switch (cell.getCellType()) {  
        case Cell.CELL_TYPE_BLANK:  
            ret = "";  
            break;  
        case Cell.CELL_TYPE_BOOLEAN:  
            ret = String.valueOf(cell.getBooleanCellValue());  
            break;  
        case Cell.CELL_TYPE_ERROR:  
            ret = null;  
            break;  
        case Cell.CELL_TYPE_FORMULA:  
            Workbook wb = cell.getSheet().getWorkbook();  
            CreationHelper crateHelper = wb.getCreationHelper();  
            FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();  
            ret = getCellValue(evaluator.evaluateInCell(cell));  
            break;  
        case Cell.CELL_TYPE_NUMERIC:  
            if (DateUtil.isCellDateFormatted(cell)) {   
            	SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date theDate = cell.getDateCellValue();  
                ret = simpleDateFormat.format(theDate);  
            } else {   
                ret = NumberToTextConverter.toText(cell.getNumericCellValue());  
            }  
            break;  
        case Cell.CELL_TYPE_STRING:  
            ret = cell.getRichStringCellValue().getString();  
            break;  
        default:  
            ret = null;  
        }  
          
        return ret; //有必要自行trim  
    }  
}  